﻿--EXEC SP_LOAD_OPEN_PO_PPV_IPV_REPORT 0
ALTER PROCEDURE [SP_LOAD_OPEN_PO_PPV_IPV_REPORT]
(
	@DUMMY INT
		
)
AS
BEGIN
		DECLARE @PROCESSSTEP_ID INT, @PO_ID INT, @LOT_ID INT, @VENDOR_ID INT,@Shipped_QTY INT,@SHIP_DATE DATETIME
		DECLARE @LOT_NO VARCHAR(50), @PO_NO	VARCHAR(50), @V_NAME VARCHAR(50),@PART_NO VARCHAR(50), @PO_DATE DATETIME
		DECLARE @QUARTER_NO		INT, @YEAR INT , @STD_COST	NUMERIC(18,4),@PO_COST	NUMERIC(18,4), @ACTUAL_COST NUMERIC(18,4)
			
		CREATE TABLE #RESULTS ( [Lot No] VARCHAR(50),[Part Number] VARCHAR(50), [Vendor] VARCHAR(50),[PO No.] VARCHAR(50),[PO Date] DATETIME,[Shipment Date] DATETIME,
		[Shipped Qty] INT, [Std Cost] NUMERIC(18,4), [PO Cost] NUMERIC(18,4), [Actual Cost] NUMERIC(18,4),
		[PPV/Wafer] NUMERIC(18,4), [IPV/Wafer] NUMERIC(18,4), [Total PPV] NUMERIC(18,4),[Total IPV] NUMERIC(18,4) )
			
		DECLARE @LOT_CURSOR CURSOR 
		SET @LOT_CURSOR = CURSOR FOR 
		SELECT LT.LOT_ID, LT.LOT_NUMBER, LT.PROCESSSTEP_ID, 
				(SELECT L_VENDOR_NAME FROM LOCATION WHERE LOCATION_ID = LT.LOCATION_ID ), SHIP_QTY, SHIP_DATE,SL_CUSTOMER_PO,Invoice_Cost FROM SHIPPED_LOT SL,LOT_TABLE LT, PROCESS_STEP PS
					WHERE SL.LOT_ID = LT.LOT_ID	AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE = 'FAB'  AND SHIP_DATE > '06/01/2010'

 		OPEN @LOT_CURSOR
		FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID, @LOT_NO ,@PROCESSSTEP_ID, @V_NAME,@Shipped_QTY, @SHIP_DATE, @PO_NO,@ACTUAL_COST
		WHILE @@FETCH_STATUS = 0
		BEGIN
				SELECT @VENDOR_ID= VENDOR_ID FROM VENDOR WHERE V_NAME = @V_NAME
				SELECT @PO_ID = PURCHASEORDER_ID, @PO_DATE = PO_DATE FROM PurchaseOrder_Table WHERE PURCHASEORDER_NO = @PO_NO
				SELECT @PART_NO = PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID

				SELECT @YEAR = CC.CC_YEAR, @QUARTER_NO = CQC_QUARTER_NO FROM COMPANY_QUARTERLY_CALENDAR CQC, COMPANY_CALENDAR CC
				WHERE CQC.CQC_YEARID = CC.CC_YEARID 
				AND @PO_DATE >= CQC_START_DATE 
				AND @PO_DATE <= CQC_END_DATE

				SELECT @PO_COST = UnitCost FROM PurchaseOrder_Table POT, PurchaseOrder_Line_Item_Table POLIT WHERE POT.PURCHASEORDER_ID = POLIT.PURCHASEORDER_ID 
							AND POT.PURCHASEORDER_ID = @PO_ID AND PROCESSSTEP_ID = @PROCESSSTEP_ID
							AND VENDOR_ID = @VENDOR_ID
				
				SELECT @STD_COST = (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
					WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
					WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
					ELSE Q4_STANDARD_COST
				 END) FROM Update_view_cost_table WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(Fin_Year) = @YEAR
				/*SELECT @STD_COST = Parameter_Value FROM PROCESS_Parameter WHERE ProcessStep_ID = @PROCESSSTEP_ID AND Parameter_Key = 'Standard Unit Cost'*/

				INSERT INTO #RESULTS ( [Lot No],[Part Number],[Vendor],[PO No.],[PO Date],[Shipment Date],[Shipped Qty],[Std Cost],[PO Cost],
							 [Actual Cost], [PPV/Wafer], [IPV/Wafer], [Total PPV],[Total IPV] )
					VALUES (@LOT_NO, @PART_NO, @V_NAME, @PO_NO, @PO_DATE, @SHIP_DATE, @Shipped_QTY, @STD_COST, @PO_COST,
							 @ACTUAL_COST,(ISNULL(@PO_COST,0.0)-ISNULl(@STD_COST,0.0)), (ISNULL(@ACTUAL_COST,0.0)-ISNULl(@PO_COST,0.0)),
							((ISNULL(@PO_COST,0.0)-ISNULl(@STD_COST,0.0))*@Shipped_QTY) , ((ISNULL(@ACTUAL_COST,0.0)-ISNULl(@PO_COST,0.0))*@Shipped_QTY) )
					
		
		SET @V_NAME			= NULL
		SET @PO_NO			= NULL
		SET @LOT_NO			= NULL
		SET @PO_DATE		= NULL	
		SET @Shipped_QTY	= NULL
		SET @SHIP_DATE		= null
		SET @PROCESSSTEP_ID = NULL
		SET @PART_NO		= NULL
		SET @YEAR			= NULL
		SET @QUARTER_NO		= NULL
		SET @STD_COST		= NULL
		SET @PO_ID			= NULL 
		SET @LOT_ID			= NULL 
		SET @VENDOR_ID		= NULL
		SET @PO_COST		= NULL
		FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID, @LOT_NO, @PROCESSSTEP_ID, @V_NAME, @Shipped_QTY, @SHIP_DATE, @PO_NO,@ACTUAL_COST
	END	
	CLOSE @LOT_CURSOR
	DEALLOCATE @LOT_CURSOR	

	SELECT [Lot No],[Part Number],[Vendor],[PO No.], CONVERT( VARCHAR,[PO Date], 101) AS [PO DATE], CONVERT(VARCHAR,[Shipment Date], 101) AS [SHIP DATE],[Shipped Qty],[Std Cost],[PO Cost],
							 [Actual Cost], [PPV/Wafer], [IPV/Wafer], [Total PPV],[Total IPV] FROM #RESULTS
END
